import { Router } from "express"
import { IcallbackFn, Ioracle } from "../../types"
import { decrypt } from "../../utils/privateKey"
const Excel = require("exceljs")
const { PassThrough } = require("stream")

interface queryForm {
  docid: string
  flow: number
  start_time: string
  end_time: string
  status: string
  formcate: number
}

export default (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  router.post("/getdisposalListCount", (_req, res) => {
    let { username } = _req.body
    let sql = `select count(*)count from xx_cf_customar_apply_header a,xx_cf_customar_apply_flow b  where a.action_klm = '${decrypt(
      username
    )}' and a.docid = b.docid  and a.current_flow = b.flow   and b.approve is null`
    sql +=
      " and (a.week_record = TO_CHAR(SYSDATE, 'YYYY') || '-' ||TO_CHAR(SYSDATE, 'IW') or a.week_record = TO_CHAR(SYSDATE, 'YYYY') || '-' ||TO_CHAR(SYSDATE-7, 'IW') )"
    sql +=
      " and (b.week_record = TO_CHAR(SYSDATE, 'YYYY') || '-' ||TO_CHAR(SYSDATE, 'IW') or b.week_record = TO_CHAR(SYSDATE, 'YYYY') || '-' ||TO_CHAR(SYSDATE-7, 'IW') )"
    sql += " and a.week_record = b.week_record"
    callbackFn(res, oracle.connChaos, { sql })
  })

  router.post("/getdisposalList", (req, res) => {
    let { username, current } = req.body
    let sql =
      "select * from (select row_number() over(order by c.docid) as rowseq,c.* from("
    sql +=
      " select a.docid,a.formtype,a.current_flow,b.flow_phase,a.last_update_by,to_char(a.last_update_time,'YYYY-MM-DD HH:MI:SS')last_update_time"
    sql += "  from xx_cf_customar_apply_header a,xx_cf_customar_apply_flow b"
    sql += "  where a.docid = b.docid  and a.current_flow = b.flow "
    sql += `  and b.approve is null  and a.action_klm = '${decrypt(username)}' 
    and (a.week_record = TO_CHAR(SYSDATE, 'YYYY') || '-' ||TO_CHAR(SYSDATE, 'IW') or a.week_record = TO_CHAR(SYSDATE, 'YYYY') || '-' ||TO_CHAR(SYSDATE-7, 'IW') )
    and (b.week_record = TO_CHAR(SYSDATE, 'YYYY') || '-' ||TO_CHAR(SYSDATE, 'IW') or b.week_record = TO_CHAR(SYSDATE, 'YYYY') || '-' ||TO_CHAR(SYSDATE-7, 'IW') )
    and a.week_record = b.week_record
    )c)`
    sql += ` where rowseq between ${(current - 1) * 7 + 1} and ${current * 7}`
    callbackFn(res, oracle.connChaos, { sql })
  })

  router.post("/getqueryList", (req, res) => {
    let { queryform, querytype, current, pagesize } = req.body
    let queryformParse: queryForm = JSON.parse(queryform)
    let sql = ""
    if (querytype === "Count") {
      sql +=
        "select count(*)count from (select row_number() over(order by c.no) as rowseq,c.* from("
    } else {
      sql +=
        "select * from (select row_number() over(order by c.no) as rowseq,c.* from("
    }
    if (queryformParse.formcate === 1) {
      sql +=
        "select b.docid no,a.status,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.department,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url,d.flow_phase"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b,xx_cf_customar_apply_flow d "
    } else if (queryformParse.formcate === 2 || queryformParse.formcate === 7) {
      sql +=
        "select b.docid no,a.status,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url,d.flow_phase"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b,xx_cf_customar_apply_flow d "
    } else if (queryformParse.formcate === 3) {
      sql +=
        "select b.docid no,a.status,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,b.department,a.appendix_url,d.flow_phase"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b,xx_cf_customar_apply_flow d "
    } else if (queryformParse.formcate === 4) {
      sql +=
        "select b.docid no,a.status,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.findings,b.department,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url,d.flow_phase"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b,xx_cf_customar_apply_flow d "
    } else if (queryformParse.formcate === 5) {
      sql +=
        "select b.docid no,a.status,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.lob,b.project_code,b.apn,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url,d.flow_phase"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b,xx_cf_customar_apply_flow d "
    } else if (queryformParse.formcate === 6) {
      sql += `select b.docid no,b.no month_record,b.issue_title,b.issue_failure_rate,b.downstrean_node,b.issue_description,b.application,b.lob,b.board_name
             ,b.code,b.apn,b.revision,b.aftd,to_char(b.issue_opening_date,'yyyy-mm-dd')issue_opening_date,to_char(b.issue_closing_date,'yyyy-mm-dd')issue_closing_date
             ,to_char(b.issue_occurrence,'yyyy-mm-dd')issue_occurrence,b.issue_build_stage,b.severity,b.rc_responsibility
             ,b.this_week_update,b.next_action,b.check_point,b.next_action_dri,b.url`
      sql += ",a.appendix_url,d.flow_phase "
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_fpcdashboard b,xx_cf_customar_apply_flow d "
    }
    sql +=
      "  where a.docid = b.docid and a.docid = d.docid and a.current_flow = d.flow "
    if (queryformParse.docid !== "") {
      sql += ` and a.docid = '${queryformParse.docid}'`
    }
    if (queryformParse.flow) {
      sql += ` and a.current_flow =${queryformParse.flow}`
    }
    if (queryformParse.formcate) {
      sql += ` and a.formtype=${queryformParse.formcate}`
    }
    if (queryformParse.status !== "") {
      sql += ` and a.status='${queryformParse.status}'`
    }
    if (queryformParse.start_time !== "") {
      sql += ` and a.created_date to_date('${queryformParse.start_time}','YYYY-MM-DD')`
    }
    if (queryformParse.end_time !== "") {
      sql += ` and a.created_date <= to_date('${queryformParse.start_time}','YYYY-MM-DD')`
    }

    //由於第二次改動需要查到CF168CQE的流程，已經不好加在前面的sql中了，選擇Union
    sql += " union "

    if (queryformParse.formcate === 1) {
      sql +=
        "select b.docid no,a.status,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.department,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url,'Information Maintaince' flow_phase"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b"
    } else if (queryformParse.formcate === 2 || queryformParse.formcate === 7) {
      sql +=
        "select b.docid no,a.status,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url,'Information Maintaince' flow_phase"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (queryformParse.formcate === 3) {
      sql +=
        "select b.docid no,a.status,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,b.department,a.appendix_url,'Information Maintaince' flow_phase"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b"
    } else if (queryformParse.formcate === 4) {
      sql +=
        "select b.docid no,a.status,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.findings,b.department,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url,'Information Maintaince' flow_phase"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b"
    } else if (queryformParse.formcate === 5) {
      sql +=
        "select b.docid no,a.status,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.lob,b.project_code,b.apn,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url,'Information Maintaince' flow_phase"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (queryformParse.formcate === 6) {
      sql += ` select b.docid no,b.no month_record,b.issue_title,b.issue_failure_rate,b.downstrean_node,b.issue_description,b.application,b.lob,b.board_name
             ,b.code,b.apn,b.revision,b.aftd,to_char(b.issue_opening_date,'yyyy-mm-dd')issue_opening_date,to_char(b.issue_closing_date,'yyyy-mm-dd')issue_closing_date
             ,to_char(b.issue_occurrence,'yyyy-mm-dd')issue_occurrence,b.issue_build_stage,b.severity,b.rc_responsibility
             ,b.this_week_update,b.next_action,b.check_point,b.next_action_dri,b.url`
      sql += ",a.appendix_url,'Information Maintaince' flow_phase"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_fpcdashboard b "
    }
    sql +=
      "  where a.docid = b.docid  and a.current_flow = 10 and a.action_klm = 'xdomain\\cf168cqe' "
    if (queryformParse.docid !== "") {
      sql += ` and a.docid = '${queryformParse.docid}'`
    }
    if (queryformParse.flow) {
      sql += ` and a.current_flow =${queryformParse.flow}`
    }
    if (queryformParse.formcate) {
      sql += ` and a.formtype=${queryformParse.formcate}`
    }
    if (queryformParse.status !== "") {
      sql += ` and a.status='${queryformParse.status}'`
    }
    if (queryformParse.start_time !== "") {
      sql += ` and a.created_date to_date('${queryformParse.start_time}','YYYY-MM-DD')`
    }
    if (queryformParse.end_time !== "") {
      sql += ` and a.created_date <= to_date('${queryformParse.start_time}','YYYY-MM-DD')`
    }

    sql += " )c)"
    if (querytype === "List")
      sql += ` where rowseq between ${(current - 1) * pagesize + 1} and ${
        current * pagesize
      }`
    callbackFn(res, oracle.connChaos, { sql })
  })

  router.post("/getqueryDetail", (req, res) => {
    let { docid, formtype } = req.body
    let sql = ""
    //
    if (formtype === 1) {
      sql +=
        "select b.docid,a.status,b.seq,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.department,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (formtype === 2 || formtype === 7) {
      sql +=
        "select b.docid,a.status,b.seq,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (formtype === 3) {
      sql +=
        "select b.docid,a.status,b.seq,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,b.department,a.appendix_url"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (formtype === 4) {
      sql +=
        "select b.docid,a.status,b.seq,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.findings,b.department,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (formtype === 5) {
      sql +=
        "select b.docid,a.status,b.seq,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.lob,b.project_code,b.apn,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.appendix_url"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (formtype === 6) {
      sql += `select b.docid no,b.seq,b.no month_record,b.issue_title,b.issue_failure_rate,b.downstrean_node,b.issue_description,b.application,b.lob,b.board_name
             ,b.code,b.apn,b.revision,b.aftd,to_char(b.issue_opening_date,'yyyy-mm-dd')issue_opening_date,to_char(b.issue_closing_date,'yyyy-mm-dd')issue_closing_date
             ,to_char(b.issue_occurrence,'yyyy-mm-dd')issue_occurrence,b.issue_build_stage,b.severity,b.rc_responsibility
             ,b.this_week_update,b.next_action,b.check_point,b.next_action_dri,b.url,`
      sql +=
        "a.status,a.appendix_url from xx_cf_customar_apply_header a,xx_cf_customar_fpcdashboard b "
    }
    sql += ` where a.docid = b.docid and a.docid = '${docid}' and b.week_record= a.week_record order by seq`
    callbackFn(res, oracle.connChaos, { sql })
  })

  //用來做驗證，狀態結案不可再編輯
  router.post("/getdocidStatus", (req, res) => {
    const { docid } = req.body
    let sql = `select status,current_flow,formtype from xx_cf_customar_apply_header where docid = '${docid.trim()}'`
    callbackFn(res, oracle.connChaos, { sql })
  })
  //如果第二週流程還未開啟，則不允許編輯
  router.post("/getActionKLM", (req, res) => {
    const { docid } = req.body
    let sql = `  select a.action_klm from xx_cf_customar_apply_header a`
    sql += ` where a.docid = '${docid}'`
    oracle
      .connChaos({ sql })
      .then((result) => {
        //console.log(result.data[0].action_klm)
        if (result.data[0].action_klm === "xdomain\\cf168cqe") {
          sql = ` select count (*) count from xx_cf_customar_apply_flow where docid ='${docid}' and action_klm = 'xdomain\\cf168cqe'`
          oracle
            .connChaos({ sql })
            .then((result) => {
              if ((result.data[0].count as number) > 0) {
                res.send({
                  code: 200,
                  message: "Success",
                  data: [{ message: "Success" }],
                })
              } else {
                res.send({
                  code: 200,
                  message: "Fail",
                  data: [{ message: "Fail" }],
                })
              }
            })
            .catch(() => {
              res.send({
                code: 0,
                message: "database error!",
              })
            })
        } else {
          res.send({
            code: 200,
            message: "Success",
            data: [{ message: "Success" }],
          })
        }
      })
      .catch(() => {
        res.send({
          code: 0,
          message: "database error!",
        })
      })
  })
  //流程審核不是本人無法申請
  router.post("/getflowStatus", (req, res) => {
    const { docid, username } = req.body
    let sql = `select count(*)count,formtype from xx_cf_customar_apply_header where docid = '${docid}' and action_klm = '${decrypt(
      username
    )}' and current_flow <> 10`
    sql += " group by formtype"
    callbackFn(res, oracle.connChaos, { sql })
  })

  router.post("/getqueryFlow", (req, res) => {
    const { docid } = req.body
    let sql = `select b.flow seq,b.flow_phase,b.action_klm,b.approve,b.comments,to_char(b.last_update_time,'YYYY-MM-DD')last_update_date from xx_cf_customar_apply_flow b,xx_cf_customar_apply_header a where a.docid = '${docid}' and a.week_record = b.week_record and a.docid = b.docid`
    sql += ` order by b.last_update_time`
    callbackFn(res, oracle.connChaos, { sql })
  })

  router.post("/getExcel", (req, res) => {
    const { queryform } = req.body
    let workbook = new Excel.Workbook()
    let worksheet = workbook.addWorksheet("sheet 1")
    let queryformParse: queryForm = JSON.parse(queryform)
    let sql = ""
    if (queryformParse.formcate === 1) {
      worksheet.columns = [
        { header: "no", key: "docid" },
        { header: "item", key: "seq" },
        { header: "occur_date", key: "occur_date" },
        { header: "auditor", key: "auditor" },
        { header: "department", key: "department" },
        { header: "findings", key: "findings" },
        { header: "improvement_action", key: "improvement_action" },
        { header: "dri", key: "dri" },
        { header: "implementation_date", key: "implementation_date" },
        { header: "status", key: "status" },
      ]
      sql +=
        "select b.docid,b.seq,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.department,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.status"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (queryformParse.formcate === 2 || queryformParse.formcate === 7) {
      worksheet.columns = [
        { header: "no", key: "docid" },
        { header: "item", key: "seq" },
        { header: "occur_date", key: "occur_date" },
        { header: "auditor", key: "auditor" },
        { header: "category", key: "category" },
        { header: "findings", key: "findings" },
        { header: "improvement_action", key: "improvement_action" },
        { header: "dri", key: "dri" },
        { header: "implementation_date", key: "implementation_date" },
        { header: "status", key: "status" },
      ]
      sql +=
        "select b.docid,b.seq,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.status"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (queryformParse.formcate === 3) {
      worksheet.columns = [
        { header: "no", key: "docid" },
        { header: "item", key: "seq" },
        { header: "occur_date", key: "occur_date" },
        { header: "auditor", key: "auditor" },
        { header: "category", key: "category" },
        { header: "findings", key: "findings" },
        { header: "improvement_action", key: "improvement_action" },
        { header: "dri", key: "dri" },
        { header: "implementation_date", key: "implementation_date" },
        { header: "status", key: "status" },
      ]
      sql +=
        "select b.docid,b.seq,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.findings,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,b.department,a.status"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (queryformParse.formcate === 4) {
      worksheet.columns = [
        { header: "no", key: "docid" },
        { header: "item", key: "seq" },
        { header: "occur_date", key: "occur_date" },
        { header: "auditor", key: "auditor" },
        { header: "category", key: "category" },
        { header: "department", key: "department" },
        { header: "findings", key: "findings" },
        { header: "improvement_action", key: "improvement_action" },
        { header: "dri", key: "dri" },
        { header: "implementation_date", key: "implementation_date" },
        { header: "status", key: "status" },
      ]
      sql +=
        "select b.docid,b.seq,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.auditor,b.category,b.department,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.status"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (queryformParse.formcate === 5) {
      worksheet.columns = [
        { header: "no", key: "docid" },
        { header: "item", key: "seq" },
        { header: "occur_date", key: "occur_date" },
        { header: "lob", key: "lob" },
        { header: "project_code", key: "project_code" },
        { header: "apn", key: "apn" },
        { header: "findings", key: "findings" },
        { header: "improvement_action", key: "improvement_action" },
        { header: "dri", key: "dri" },
        { header: "implementation_date", key: "implementation_date" },
        { header: "status", key: "status" },
      ]
      sql +=
        "select b.docid,b.seq,to_char(b.occur_date,'YYYY-MM-DD')occur_date,b.lob,b.project_code,b.apn,b.improvement_actions improvement_action "
      sql +=
        ",b.dri,to_char(b.implemention_date,'YYYY-MM-DD')implementation_date,a.status"
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_auditor_lines b "
    } else if (queryformParse.formcate === 6) {
      worksheet.columns = [
        { header: "no", key: "docid" },
        { header: "item", key: "seq" },
        { header: "lob", key: "lob" },
        { header: "issue_title", key: "issue_title" },
        { header: "issue_failure_rate", key: "issue_failure_rate" },
        { header: "downstrean_node", key: "downstrean_node" },
        { header: "issue_description", key: "issue_description" },
        { header: "application", key: "application" },
        { header: "board_name", key: "board_name" },
        { header: "apn", key: "apn" },
        { header: "code", key: "code" },
        { header: "revision", key: "revision" },
        { header: "apple fab team dri", key: "aftd" },
        { header: "issue_opening_date", key: "issue_opening_date" },
        { header: "issue_closing_date", key: "issue_closing_date" },
        { header: "issue_occurrence", key: "issue_occurrence" },
        { header: "issue_build_stage", key: "issue_build_stage" },
        { header: "severity", key: "severity" },
        { header: "rc_responsibility", key: "rc_responsibility" },
        { header: "this_week_update", key: "this_week_update" },
        { header: "next_action", key: "next_action" },
        { header: "check_point", key: "check_point" },
        { header: "next_action_dri", key: "next_action_dri" },
        { header: "week_record", key: "week_record" },
        { header: "url link to 8D", key: "url" },
      ]
      sql += `select b.docid no,b.no month_record,b.issue_title,b.issue_failure_rate,b.downstrean_node,b.issue_description,b.application,b.lob,b.board_name
             ,b.code,b.apn,b.revision,b.aftd,to_char(b.issue_opening_date,'yyyy-mm-dd')issue_opening_date,to_char(b.issue_closing_date,'yyyy-mm-dd')issue_closing_date
             ,to_char(b.issue_occurrence,'yyyy-mm-dd')issue_occurrence,b.issue_build_stage,b.severity,b.rc_responsibility
             ,b.this_week_update,b.next_action,b.check_point,b.next_action_dri,b.url,b.week_record`
      sql +=
        " from xx_cf_customar_apply_header a,xx_cf_customar_fpcdashboard b "
    }
    sql += ` where a.formtype=${queryformParse.formcate} and a.docid = b.docid and a.week_record = b.week_record`
    if (queryformParse.docid !== "") {
      sql += ` and a.docid = '${queryformParse.docid}'`
    }
    if (queryformParse.flow) {
      sql += ` and a.current_flow =${queryformParse.flow}`
    }

    if (queryformParse.status !== "") {
      sql += ` and a.status='${queryformParse.status}'`
    }
    if (queryformParse.start_time !== "") {
      sql += ` and a.created_date to_date('${queryformParse.start_time}','YYYY-MM-DD')`
    }
    if (queryformParse.end_time !== "") {
      sql += ` and a.created_date <= to_date('${queryformParse.start_time}','YYYY-MM-DD')`
    }
    oracle
      .connChaos({ sql })
      .then((result) => {
        if (result.code === 200) {
          result.data.forEach(async (element) => {
            worksheet.addRow(element)
          })
          const bufferStream = PassThrough()
          workbook.xlsx
            .write(bufferStream)
            .then(() => {
              res.setHeader(
                "Content-Disposition",
                "attachment; filename=export.xlsx"
              )
              res.setHeader(
                "Content-Type",
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
              )
              res.send(bufferStream.read())
            })
            .catch(() => {
              res.send({
                code: 0,
                message: "export error",
              })
            })
        }
      })
      .catch(() => {
        res.send({
          code: 0,
          message: "database error",
        })
      })
  })
  return router
}
